|
 |
 |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
System Load
In the batch processing system, you normally try to run the system at 100 percent during the batch job. In contrast, in an OLTP system, you hold some system capacity in reserve for peak times or checkpoints. Unlike OLTP systems, where there are many users with small queries, a batch processing system typically has only one job running on the system. This job should take advantage of the entire capacity of the system. By avoiding I/O bottlenecks, this is achievable.
NOTE: Usually, I see systems configured with an I/O capacity not suitable for their required tasks. By having an I/O capacity insufficient for your work load, you will most likely see a significant I/O bottleneck, which can severely degrade performance.
The following list shows some of the typical load characteristics of a batch processing system:
- A small number of processes on the system. This is true unless you are taking advantage of the Parallel Query option, which adds more processes and subsequently more process switches.
- Minimal network traffic during transaction processing and significant network traffic during load and unload. Typically, there is very little network traffic during the time the batch transactions are running. If you use a LAN to load and unload the database, there is significant network traffic during those times.
- Heavy I/O usage. Batch processing systems usually generate large amounts of I/O to the data files. These I/Os may be somewhat random during the transaction processing phase of the job but are sequential during the load and index creation phases. If you can take advantage of the Oracle Parallel Query option, you will see more random access to the index files.
- Moderate to heavy redo log usage. The amount of load on the system caused by use of the redo log depends on the type of transactions you are running.
- Moderate to heavy use of rollback segments. The amount of load on the system caused by use of rollback segments depends on your transactions.
- Moderate to large amounts of memory. In some batch processing jobs, a large amount of memory is used; in other jobs, only a small amount of memory is necessary. Things like large sorts and joins can take advantage of large amounts of memory. Exports and direct loads do not need significant amounts of RAM.
By looking at the characteristics of a batch processing system, you can make decisions about how some design ideas can help performance. But before you look at some design ideas, you should first set the goals you want to achieve from the design.
Goals
The goal in tuning a batch processing system is to build a system that has certain characteristics. Here are the characteristics of an optimally tuned batch processing system:
- The system should be CPU bound. When you remove all other bottlenecks, the system should be able to process at its limits: the speed of the CPUs.
- The system is not disk bound. Any disk bottleneck degrade performance. If this is the case, you should add more or faster disks.
- Memory is sufficient. If the machine is paging or swapping, performance is being severely degraded. The best alternative is to add more memory; if that is not possible, reduce the size of the SGA or the number of users until the system no longer pages or swaps.
- The system meets any additional requirements you have. With many batch processing systems, the work must be completed within a certain time. You may have this or some other constraint on your system.
Goal setting is always a good idea. By setting goals, you can judge the success or failure of the system in a quantitative manner. Goal setting can also help you properly size the system necessary for the job.
Review of Batch Processing Characteristics
By characterizing the system and examining the various aspects of your particular workload, you can design a system that takes advantage of your systems particular qualities. Each system has its own characteristics and workload and should be designed to take advantage of them.
Making generalizations about a batch processing system is practically impossible because of the different types of jobs done in batch. The processes your particular system does should determine your particular configuration.
Your specific batch job may involve only query processing or may involve large numbers of updates. You may or may not do large joins or sorts. Batch is such a general term that it is impossible to make specific assumptions about what you will be doing.
Here are the particular assumptions this chapter makes:
- Database loads and unloads are a part of the batch processing job. Loads may occur through import or using the database loader. The term unload refers to exports or backups or some other method of extracting the data from the database.
- Index creation is part of the batch processing job. After the data has been loaded, indexes are created.
- Only one job is running on the machine at a time. No concurrent jobs are being done. This is not to say that the batch job does not perform various functions; rather, the concurrency of transactions can be determined by the database administrator and application designer.
Because batch systems have such diverse functions, I recommend that you examine the design suggestions in the next section and see how they may or may not relate to your system configuration. By doing so, you can determine which ideas will work for you and which will not.
Your system design should be based on the unique characteristics of your application. Remember that the more time you spend up-front in database and application design, the more optimized your system will be. The combination of well-tuned hardware, OS, and RDBMS contributes to the performance of the system as a whole.
|
|